import pymysql
from typing import List, Dict, Any, Optional, Tuple
import logging

logger = logging.getLogger(__name__)

class GoodsRepositoryBackup:
    def __init__(self):
        self.connection = None
    
    def get_connection(self):
        """获取数据库连接"""
        if not self.connection:
            self.connection = pymysql.connect(
                host='localhost',
                port=3306,
                user='your_username',
                password='your_password',
                database='your_database',
                charset='utf8mb4',
                collation='utf8mb4_unicode_ci',
                autocommit=True
            )
        return self.connection
    
    def get_goods_list(self, page=1, page_size=10, **filters):
        """获取商品列表 - 使用子查询避免JOIN冲突"""
        # 使用子查询避免JOIN校对规则冲突
        query = """
        SELECT g.*, 
               (SELECT brand_name FROM goods_brand WHERE brand_id = g.brand_id) as brand_name
        FROM goods g 
        WHERE g.deleted != '1'
        """
        
        params = []
        
        # 添加过滤条件
        if filters.get('keyword'):
            query += " AND (g.goods_name LIKE %s OR g.goods_no LIKE %s)"
            keyword = f"%{filters['keyword']}%"
            params.extend([keyword, keyword])
        
        if filters.get('brand_id'):
            query += " AND g.brand_id = %s"
            params.append(filters['brand_id'])
        
        if filters.get('class_id'):
            query += " AND g.class_id = %s"
            params.append(filters['class_id'])
        
        # 添加分页
        query += " ORDER BY g.created DESC LIMIT %s OFFSET %s"
        params.extend([page_size, (page - 1) * page_size])
        
        # 执行查询
        return self._execute_query(query, tuple(params) if params else None)
    
    def _execute_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict[str, Any]]:
        """执行查询"""
        connection = self.get_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        try:
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
        except Exception as e:
            logger.error(f"查询执行失败: {e}")
            logger.error(f"查询语句: {query}")
            logger.error(f"参数: {params}")
            raise
        finally:
            cursor.close()
    
    def close_connection(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None 